import pandas as pd
import pymysql
from sqlalchemy import create_engine, text
from docx import Document
from docx.shared import Inches, Pt, RGBColor
from docx.enum.text import WD_ALIGN_PARAGRAPH
from docx.oxml.ns import qn
from docx.oxml import parse_xml
import os
from datetime import datetime
import json

# 数据库配置（请修改为你的配置）
DB_CONFIG = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "",  # 替换为你的密码
    "db": "stock_db",
    "charset": "utf8mb4"
}

# 图片类型映射 - 根据实际的数据库记录进行修正
business_keywords = {
    '对比图表': [('comparison', 'all')],
    '季K线图表': [('individual', 'quarterly')],
    '年K线图表': [('individual', 'yearly')],
    '月K线蜡烛图': [('candle', 'monthly')]
}


def create_database_connection():
    """创建数据库连接"""
    try:
        engine = create_engine(
            f"mysql+pymysql://{DB_CONFIG['user']}:{DB_CONFIG['password']}@{DB_CONFIG['host']}:{DB_CONFIG['port']}/{DB_CONFIG['db']}?charset={DB_CONFIG['charset']}"
        )
        return engine
    except Exception as e:
        print(f"数据库连接失败: {str(e)}")
        raise


def read_company_detail_data(engine, limit=None):
    """从company_detail_analysis表读取公司数据"""
    try:
        query = """
                       SELECT 
            id AS '主键ID',
            stock_code AS '股票代码',
			company_name 上市公司名称,
            establishment_date AS '成立日期',
            manager AS '管理者',
            manager_introduction AS '管理者介绍',
            registered_address AS '注册地址',
            company_type AS '公司性质',
            primary_industry AS '一级行业',
            secondary_industry AS '二级行业',
            tertiary_industry AS '三级行业',
            business_scope AS '经营范围',
            main_business AS '主营业务',
            violation_time AS '违规时间',
            violation_type AS '违规性质',
            violation_description AS '违规描述',
            listing_date AS '上市时间',
            controlling_shareholder AS '控股股东',
            business_development_analysis AS '业务发展分析',
            financial_condition_analysis AS '财务状况分析',
            profit_model AS '盈利模式',
            moat AS '护城河',
            customer_group AS '客户群体',
            competitors AS '竞争对手',
            business_area AS '业务区域',
            subsidiaries AS '子企业',
            core_products_services AS '核心产品或服务',
            related_upstream_downstream_listed_companies AS '关联上下游上市企业',
            main_factors_affecting_operation AS '经营状况主要影响因素',
            former_names AS '企业曾用名',
            csrc_warning_letters AS '被证监会发过警告函情况',
            major_shareholder_reduction AS '大股东减持情况',
            company_goodwill AS '公司商誉',
            goodwill_scale AS '商誉规模',
            asset_provision_information AS '资产计提信息',
            position_in_subdivision_industry AS '细分行业中地位',
            debt_ratio_5years AS '近5年的负债率',
            revenue_5years AS '近5年的营业收入',
            deducted_net_profit_5years AS '近5年的扣费净利润',
            gross_profit_margin_5years AS '近5年的毛利率',
            same_industry_market_value_ranking AS '同行业市值排名公司',
            dominant_products_services_in_market AS '市场中占主导地位的产品服务',
            company_highlights AS '公司亮点',
            company_risk_points AS '公司风险点',
            themes_concepts AS '题材/概念',
            what_company_does AS '公司干什么的',
            major_customers AS '主要客户',
            stock_price_surge_periods AS '股票大涨时间段',
            performance_indicators_during_surge AS '股票大涨时间段内业绩指标详细描述',
            is_cyclical AS '是否为周期股',
            stock_price_characteristics AS '此公司的股价变动特点',
            analysis_time AS '分析时间',
            batch_id AS '批次ID',
            created_at AS '创建时间'
        FROM company_detail_analysis
        """
        if limit:
            query += f" LIMIT {limit}"

        df = pd.read_sql(query, engine)
        print(f"成功读取 {len(df)} 条公司详细数据")
        return df
    except Exception as e:
        print(f"读取数据失败: {str(e)}")
        raise


def get_stock_images_from_db(engine, stock_code):
    """从stock_chart_images表获取股票的图片路径"""
    try:
        # 使用SQLAlchemy的text函数来安全地构建查询
        query = text("""
        SELECT chart_type, period, image_path 
        FROM stock_chart_images 
        WHERE stock_code = :stock_code
        ORDER BY chart_type, period
        """)

        # 使用连接执行查询
        with engine.connect() as connection:
            result = connection.execute(query, {'stock_code': stock_code})
            rows = result.fetchall()

        # 转换为DataFrame
        if rows:
            df = pd.DataFrame(rows, columns=['chart_type', 'period', 'image_path'])
            return df
        else:
            return pd.DataFrame()

    except Exception as e:
        print(f"获取股票图片失败（{stock_code}）: {str(e)}")
        return pd.DataFrame()


def set_chinese_font(run, font_name='微软雅黑'):
    """设置中文字体"""
    run.font.name = font_name
    run._element.rPr.rFonts.set(qn('w:eastAsia'), font_name)


def add_company_header(doc, company_data):
    """添加公司头部信息"""
    # 主标题
    title = doc.add_heading('', level=0)
    title_run = title.add_run(f"{company_data['上市公司名称']}（{company_data['股票代码']}）分析报告")
    title_run.font.size = Pt(18)
    title_run.bold = True
    title_run.font.color.rgb = RGBColor(0, 0, 128)  # 深蓝色
    set_chinese_font(title_run)
    title.alignment = WD_ALIGN_PARAGRAPH.CENTER

    doc.add_paragraph()  # 空行

    # 基本信息表格 - 优化为更美观的格式
    table = doc.add_table(rows=6, cols=4)
    table.style = 'Light Grid Accent 1'
    table.autofit = False

    # 设置列宽
    for row in table.rows:
        row.cells[0].width = Inches(1.2)
        row.cells[1].width = Inches(2)
        row.cells[2].width = Inches(1.2)
        row.cells[3].width = Inches(2)

    # 设置表格基本信息
    info_data = [
        ('公司名称', company_data['上市公司名称'], '股票代码', company_data['股票代码']),
        ('成立日期', company_data['成立日期'] if pd.notna(company_data['成立日期']) and str(
            company_data['成立日期']) != '暂无信息' else '暂无',
         '上市时间', company_data['上市时间'] if pd.notna(company_data['上市时间']) and str(
            company_data['上市时间']) != '暂无信息' else '暂无'),
        ('公司性质', company_data['公司性质'] if pd.notna(company_data['公司性质']) and str(
            company_data['公司性质']) != '暂无信息' else '暂无',
         '控股股东', company_data['控股股东'] if pd.notna(company_data['控股股东']) and str(
            company_data['控股股东']) != '暂无信息' else '暂无'),
        ('注册地址', company_data['注册地址'] if pd.notna(company_data['注册地址']) and str(
            company_data['注册地址']) != '暂无信息' else '暂无',
         '企业曾用名', company_data['企业曾用名'] if pd.notna(company_data['企业曾用名']) and str(
            company_data['企业曾用名']) != '暂无信息' else '暂无'),
        ('一级行业', company_data['一级行业'] if pd.notna(company_data['一级行业']) and str(
            company_data['一级行业']) != '暂无信息' else '暂无',
         '二级行业', company_data['二级行业'] if pd.notna(company_data['二级行业']) and str(
            company_data['二级行业']) != '暂无信息' else '暂无'),
        ('三级行业', company_data['三级行业'] if pd.notna(company_data['三级行业']) and str(
            company_data['三级行业']) != '暂无信息' else '暂无',
         '细分行业地位', company_data['细分行业中地位'] if pd.notna(company_data['细分行业中地位']) and str(
            company_data['细分行业中地位']) != '暂无信息' else '暂无')
    ]

    for i, (label1, value1, label2, value2) in enumerate(info_data):
        row_cells = table.rows[i].cells
        row_cells[0].text = label1
        row_cells[1].text = str(value1)
        row_cells[2].text = label2
        row_cells[3].text = str(value2)

        # 设置字体和样式
        for j, cell in enumerate(row_cells):
            for paragraph in cell.paragraphs:
                for run in paragraph.runs:
                    set_chinese_font(run)
                    if j % 2 == 0:  # 标签单元格加粗
                        run.bold = True
                        run.font.color.rgb = RGBColor(0, 0, 0)  # 黑色
                    else:
                        run.font.color.rgb = RGBColor(64, 64, 64)  # 深灰色

    doc.add_paragraph()  # 空行


def add_company_overview(doc, company_data):
    """添加公司概况"""
    doc.add_heading('一、公司概况', level=1)

    # 公司是做什么的 - 突出显示
    if pd.notna(company_data['公司干什么的']) and str(company_data['公司干什么的']).strip() and str(
            company_data['公司干什么的']) != '暂无信息':
        p = doc.add_paragraph()
        p.add_run("公司定位：").bold = True
        set_chinese_font(p.runs[0])
        run = p.add_run(str(company_data['公司干什么的']))
        set_chinese_font(run)
        run.font.color.rgb = RGBColor(0, 0, 128)  # 深蓝色
        run.italic = True

    # 主营业务和经营范围
    if pd.notna(company_data['主营业务']) and str(company_data['主营业务']).strip() and str(
            company_data['主营业务']) != '暂无信息':
        doc.add_heading('主营业务', level=2)
        p = doc.add_paragraph(str(company_data['主营业务']))
        set_chinese_font(p.runs[0])

    if pd.notna(company_data['经营范围']) and str(company_data['经营范围']).strip() and str(
            company_data['经营范围']) != '暂无信息':
        doc.add_heading('经营范围', level=2)
        # 处理经营范围文本，添加适当的格式
        business_scope = str(company_data['经营范围'])
        if '；' in business_scope or '。' in business_scope:
            # 按分号或句号分割，每项单独一行
            items = business_scope.replace('；', ';').replace('。', '.').split(';')
            for item in items:
                if item.strip() and item.strip() != '.':
                    p = doc.add_paragraph(style='List Bullet')
                    p.add_run(item.strip())
                    set_chinese_font(p.runs[0])
        else:
            p = doc.add_paragraph(business_scope)
            set_chinese_font(p.runs[0])


def add_business_analysis(doc, company_data):
    """添加业务分析"""
    doc.add_heading('二、业务分析', level=1)

    # 核心产品或服务
    if pd.notna(company_data['核心产品或服务']) and str(company_data['核心产品或服务']).strip() and str(
            company_data['核心产品或服务']) != '暂无信息':
        doc.add_heading('核心产品或服务', level=2)
        content = str(company_data['核心产品或服务'])
        if '、' in content or ',' in content:
            # 按顿号或逗号分割，创建表格
            items = content.replace(',', '、').split('、')
            table = doc.add_table(rows=1, cols=1)
            table.style = 'Light Grid Accent 1'
            hdr_cells = table.rows[0].cells
            hdr_cells[0].text = '核心产品/服务'
            for paragraph in hdr_cells[0].paragraphs:
                for run in paragraph.runs:
                    set_chinese_font(run)
                    run.bold = True

            for item in items:
                if item.strip():
                    row_cells = table.add_row().cells
                    row_cells[0].text = item.strip()
                    for paragraph in row_cells[0].paragraphs:
                        for run in paragraph.runs:
                            set_chinese_font(run)
        else:
            p = doc.add_paragraph(content)
            set_chinese_font(p.runs[0])

    # 市场中占主导地位的产品/服务
    if pd.notna(company_data['市场中占主导地位的产品服务']) and str(
            company_data['市场中占主导地位的产品服务']).strip() and str(
        company_data['市场中占主导地位的产品服务']) != '暂无信息':
        doc.add_heading('主导产品/服务', level=2)
        p = doc.add_paragraph(str(company_data['市场中占主导地位的产品服务']))
        set_chinese_font(p.runs[0])
        # 添加下划线强调
        p.runs[0].underline = True

    # 盈利模式
    if pd.notna(company_data['盈利模式']) and str(company_data['盈利模式']).strip() and str(
            company_data['盈利模式']) != '暂无信息':
        doc.add_heading('盈利模式', level=2)
        p = doc.add_paragraph(str(company_data['盈利模式']))
        set_chinese_font(p.runs[0])


def add_market_analysis(doc, company_data):
    """添加市场分析"""
    doc.add_heading('三、市场分析', level=1)

    # 客户群体和主要客户 - 使用表格
    if pd.notna(company_data['客户群体']) and str(company_data['客户群体']).strip() and str(
            company_data['客户群体']) != '暂无信息':
        doc.add_heading('客户群体', level=2)
        p = doc.add_paragraph(str(company_data['客户群体']))
        set_chinese_font(p.runs[0])

    if pd.notna(company_data['主要客户']) and str(company_data['主要客户']).strip() and str(
            company_data['主要客户']) != '暂无信息':
        doc.add_heading('主要客户', level=2)
        p = doc.add_paragraph(str(company_data['主要客户']))
        set_chinese_font(p.runs[0])

    # 业务区域
    if pd.notna(company_data['业务区域']) and str(company_data['业务区域']).strip() and str(
            company_data['业务区域']) != '暂无信息':
        doc.add_heading('业务区域', level=2)
        p = doc.add_paragraph(str(company_data['业务区域']))
        set_chinese_font(p.runs[0])

    # 护城河
    if pd.notna(company_data['护城河']) and str(company_data['护城河']).strip() and str(
            company_data['护城河']) != '暂无信息':
        doc.add_heading('护城河', level=2)
        content = str(company_data['护城河'])
        if '、' in content or ',' in content:
            # 按顿号或逗号分割，创建项目列表
            items = content.replace(',', '、').split('、')
            for item in items:
                if item.strip():
                    p = doc.add_paragraph(style='List Bullet')
                    p.add_run(item.strip())
                    set_chinese_font(p.runs[0])
        else:
            p = doc.add_paragraph(content)
            set_chinese_font(p.runs[0])


def add_competitive_analysis(doc, company_data):
    """添加竞争分析"""
    doc.add_heading('四、竞争分析', level=1)

    # 竞争对手 - 使用表格
    if pd.notna(company_data['竞争对手']) and str(company_data['竞争对手']).strip() and str(
            company_data['竞争对手']) != '暂无信息':
        doc.add_heading('竞争对手', level=2)
        competitors = str(company_data['竞争对手'])
        if '、' in competitors or ',' in competitors:
            # 创建竞争对手表格
            items = competitors.replace(',', '、').split('、')
            table = doc.add_table(rows=1, cols=2)
            table.style = 'Light Grid Accent 1'
            hdr_cells = table.rows[0].cells
            hdr_cells[0].text = '序号'
            hdr_cells[1].text = '竞争对手'
            for cell in hdr_cells:
                for paragraph in cell.paragraphs:
                    for run in paragraph.runs:
                        set_chinese_font(run)
                        run.bold = True

            for idx, item in enumerate(items, 1):
                if item.strip():
                    row_cells = table.add_row().cells
                    row_cells[0].text = str(idx)
                    row_cells[1].text = item.strip()
                    for cell in row_cells:
                        for paragraph in cell.paragraphs:
                            for run in paragraph.runs:
                                set_chinese_font(run)
        else:
            p = doc.add_paragraph(competitors)
            set_chinese_font(p.runs[0])

    # 同行业市值排名公司
    if pd.notna(company_data['同行业市值排名公司']) and str(company_data['同行业市值排名公司']).strip() and str(
            company_data['同行业市值排名公司']) != '暂无信息':
        doc.add_heading('同行业公司对比', level=2)
        p = doc.add_paragraph(str(company_data['同行业市值排名公司']))
        set_chinese_font(p.runs[0])


def add_financial_analysis(doc, company_data):
    """添加财务分析"""
    doc.add_heading('五、财务分析', level=1)

    # 财务状况分析
    if pd.notna(company_data['财务状况分析']) and str(company_data['财务状况分析']).strip() and str(
            company_data['财务状况分析']) != '暂无信息':
        doc.add_heading('财务状况分析', level=2)
        p = doc.add_paragraph(str(company_data['财务状况分析']))
        set_chinese_font(p.runs[0])

    # 财务指标数据 - 使用表格
    financial_metrics = [
        ('近5年负债率', company_data['近5年的负债率']),
        ('近5年营业收入', company_data['近5年的营业收入']),
        ('近5年扣非净利润', company_data['近5年的扣费净利润']),
        ('近5年毛利率', company_data['近5年的毛利率'])
    ]

    for label, value in financial_metrics:
        if pd.notna(value) and str(value).strip() and str(value) != '暂无信息':
            doc.add_heading(label, level=2)
            try:
                # 尝试解析JSON格式的财务数据
                financial_data = json.loads(value)
                if isinstance(financial_data, dict):
                    # 创建财务数据表格
                    table = doc.add_table(rows=1, cols=2)
                    table.style = 'Light Grid Accent 1'
                    hdr_cells = table.rows[0].cells
                    hdr_cells[0].text = '年份'
                    hdr_cells[1].text = label
                    for cell in hdr_cells:
                        for paragraph in cell.paragraphs:
                            for run in paragraph.runs:
                                set_chinese_font(run)
                                run.bold = True

                    # 按年份排序
                    for year in sorted(financial_data.keys()):
                        row_cells = table.add_row().cells
                        row_cells[0].text = str(year)
                        row_cells[1].text = str(financial_data[year])
                        for cell in row_cells:
                            for paragraph in cell.paragraphs:
                                for run in paragraph.runs:
                                    set_chinese_font(run)
                else:
                    p = doc.add_paragraph(str(value))
                    set_chinese_font(p.runs[0])
            except:
                p = doc.add_paragraph(str(value))
                set_chinese_font(p.runs[0])

    # 商誉信息
    if pd.notna(company_data['公司商誉']) and str(company_data['公司商誉']).strip() and str(
            company_data['公司商誉']) != '暂无信息':
        doc.add_heading('商誉信息', level=2)
        table = doc.add_table(rows=2, cols=2)
        table.style = 'Light Grid Accent 1'

        # 表头
        hdr_cells = table.rows[0].cells
        hdr_cells[0].text = '项目'
        hdr_cells[1].text = '内容'
        for cell in hdr_cells:
            for paragraph in cell.paragraphs:
                for run in paragraph.runs:
                    set_chinese_font(run)
                    run.bold = True

        # 商誉数据
        row_cells = table.rows[1].cells
        row_cells[0].text = '商誉情况'
        row_cells[1].text = str(company_data['公司商誉'])

        goodwill_scale = company_data['商誉规模'] if pd.notna(company_data['商誉规模']) and str(
            company_data['商誉规模']) != '暂无信息' else '未知'

        # 添加商誉规模行
        row_cells = table.add_row().cells
        row_cells[0].text = '商誉规模'
        row_cells[1].text = str(goodwill_scale)

        for row in table.rows:
            for cell in row.cells:
                for paragraph in cell.paragraphs:
                    for run in paragraph.runs:
                        set_chinese_font(run)

    # 资产计提信息
    if pd.notna(company_data['资产计提信息']) and str(company_data['资产计提信息']).strip() and str(
            company_data['资产计提信息']) != '暂无信息':
        doc.add_heading('资产计提信息', level=2)
        p = doc.add_paragraph(str(company_data['资产计提信息']))
        set_chinese_font(p.runs[0])


def add_development_analysis(doc, company_data):
    """添加发展分析"""
    doc.add_heading('六、业务发展分析', level=1)

    # 业务发展分析
    if pd.notna(company_data['业务发展分析']) and str(company_data['业务发展分析']).strip() and str(
            company_data['业务发展分析']) != '暂无信息':
        p = doc.add_paragraph(str(company_data['业务发展分析']))
        set_chinese_font(p.runs[0])

    # 公司亮点 - 使用项目符号
    if pd.notna(company_data['公司亮点']) and str(company_data['公司亮点']).strip() and str(
            company_data['公司亮点']) != '暂无信息':
        doc.add_heading('公司亮点', level=2)
        highlights = str(company_data['公司亮点'])
        if '、' in highlights or ',' in highlights:
            items = highlights.replace(',', '、').split('、')
            for item in items:
                if item.strip():
                    p = doc.add_paragraph(style='List Bullet')
                    p.add_run(item.strip())
                    set_chinese_font(p.runs[0])
                    p.runs[0].font.color.rgb = RGBColor(0, 100, 0)  # 深绿色表示亮点
        else:
            p = doc.add_paragraph(highlights)
            set_chinese_font(p.runs[0])
            p.runs[0].font.color.rgb = RGBColor(0, 100, 0)  # 深绿色表示亮点

    # 题材/概念
    if pd.notna(company_data['题材/概念']) and str(company_data['题材/概念']).strip() and str(
            company_data['题材/概念']) != '暂无信息':
        doc.add_heading('题材概念', level=2)
        themes = str(company_data['题材/概念'])
        if '、' in themes or ',' in themes:
            items = themes.replace(',', '、').split('、')
            for item in items:
                if item.strip():
                    p = doc.add_paragraph(style='List Bullet')
                    p.add_run(item.strip())
                    set_chinese_font(p.runs[0])
                    p.runs[0].font.color.rgb = RGBColor(75, 0, 130)  # 靛蓝色表示概念
        else:
            p = doc.add_paragraph(themes)
            set_chinese_font(p.runs[0])
            p.runs[0].font.color.rgb = RGBColor(75, 0, 130)  # 靛蓝色表示概念


def add_risk_analysis(doc, company_data):
    """添加风险分析"""
    doc.add_heading('七、风险分析', level=1)

    # 公司风险点 - 使用项目符号
    if pd.notna(company_data['公司风险点']) and str(company_data['公司风险点']).strip() and str(
            company_data['公司风险点']) != '暂无信息':
        doc.add_heading('公司风险点', level=2)
        risks = str(company_data['公司风险点'])
        if '、' in risks or ',' in risks:
            items = risks.replace(',', '、').split('、')
            for item in items:
                if item.strip():
                    p = doc.add_paragraph(style='List Bullet')
                    p.add_run(item.strip())
                    set_chinese_font(p.runs[0])
                    p.runs[0].font.color.rgb = RGBColor(178, 34, 34)  # 砖红色表示风险
        else:
            p = doc.add_paragraph(risks)
            set_chinese_font(p.runs[0])
            p.runs[0].font.color.rgb = RGBColor(178, 34, 34)  # 砖红色表示风险

    # 经营状况主要影响因素
    if pd.notna(company_data['经营状况主要影响因素']) and str(company_data['经营状况主要影响因素']).strip() and str(
            company_data['经营状况主要影响因素']) != '暂无信息':
        doc.add_heading('经营影响因素', level=2)
        factors = str(company_data['经营状况主要影响因素'])
        if '、' in factors or ',' in factors:
            items = factors.replace(',', '、').split('、')
            for item in items:
                if item.strip():
                    p = doc.add_paragraph(style='List Bullet')
                    p.add_run(item.strip())
                    set_chinese_font(p.runs[0])
        else:
            p = doc.add_paragraph(factors)
            set_chinese_font(p.runs[0])

    # 其他风险项目
    risk_items = [
        ('违规情况', f"{company_data['违规时间']} - {company_data['违规性质']}: {company_data['违规描述']}"
        if pd.notna(company_data['违规时间']) and str(company_data['违规时间']).strip() and str(
            company_data['违规时间']) != '暂无信息' else None),
        ('证监会警告函', company_data['被证监会发过警告函情况']),
        ('大股东减持', company_data['大股东减持情况'])
    ]

    has_other_risk = False
    for label, value in risk_items:
        if value and pd.notna(value) and str(value).strip() and str(value) != '暂无信息' and str(value) != 'None':
            has_other_risk = True
            p = doc.add_paragraph()
            p.add_run(f"{label}：").bold = True
            p.add_run(str(value))
            set_chinese_font(p.runs[0])
            set_chinese_font(p.runs[1])
            p.runs[1].font.color.rgb = RGBColor(178, 34, 34)  # 砖红色表示风险

    if not any([pd.notna(company_data['公司风险点']), pd.notna(company_data['经营状况主要影响因素']), has_other_risk]):
        p = doc.add_paragraph('暂无重大风险事件记录')
        set_chinese_font(p.runs[0])


def add_corporate_structure(doc, company_data):
    """添加公司结构"""
    doc.add_heading('八、公司结构', level=1)

    # 子企业
    if pd.notna(company_data['子企业']) and str(company_data['子企业']).strip() and str(
            company_data['子企业']) != '暂无信息':
        doc.add_heading('子企业', level=2)
        p = doc.add_paragraph(str(company_data['子企业']))
        set_chinese_font(p.runs[0])

    # 关联上下游上市企业
    if pd.notna(company_data['关联上下游上市企业']) and str(company_data['关联上下游上市企业']).strip() and str(
            company_data['关联上下游上市企业']) != '暂无信息':
        doc.add_heading('关联上下游上市企业', level=2)
        p = doc.add_paragraph(str(company_data['关联上下游上市企业']))
        set_chinese_font(p.runs[0])


def add_management_info(doc, company_data):
    """添加管理层信息"""
    if pd.notna(company_data['管理者']) and str(company_data['管理者']).strip() and str(
            company_data['管理者']) != '暂无信息':
        doc.add_heading('九、管理层信息', level=1)

        # 管理者信息表格
        table = doc.add_table(rows=2, cols=2)
        table.style = 'Light Grid Accent 1'

        # 管理者姓名
        row_cells = table.rows[0].cells
        row_cells[0].text = '管理者'
        row_cells[1].text = str(company_data['管理者'])
        for cell in row_cells:
            for paragraph in cell.paragraphs:
                for run in paragraph.runs:
                    set_chinese_font(run)
                    if cell == row_cells[0]:  # 标签单元格加粗
                        run.bold = True

        # 管理者介绍
        if pd.notna(company_data['管理者介绍']) and str(company_data['管理者介绍']).strip() and str(
                company_data['管理者介绍']) != '暂无信息':
            # 合并单元格显示介绍
            if len(table.rows) < 2:
                table.add_row()
            intro_cell = table.rows[1].cells[0]
            intro_cell.text = '管理者介绍'
            intro_cell = table.rows[1].cells[1]
            intro_cell.text = str(company_data['管理者介绍'])

            for row in table.rows:
                for cell in row.cells:
                    for paragraph in cell.paragraphs:
                        for run in paragraph.runs:
                            set_chinese_font(run)
                            if cell == table.rows[1].cells[0]:  # 标签单元格加粗
                                run.bold = True


def add_stock_analysis(doc, company_data):
    """添加股票表现分析"""
    doc.add_heading('十、股票表现分析', level=1)

    # 是否为周期股 - 突出显示
    if pd.notna(company_data['是否为周期股']) and str(company_data['是否为周期股']).strip() and str(
            company_data['是否为周期股']) != '暂无信息':
        p = doc.add_paragraph()
        p.add_run("周期属性：").bold = True
        set_chinese_font(p.runs[0])
        run = p.add_run(str(company_data['是否为周期股']))
        set_chinese_font(run)
        if str(company_data['是否为周期股']) == '是':
            run.font.color.rgb = RGBColor(178, 34, 34)  # 红色强调是周期股
        else:
            run.font.color.rgb = RGBColor(0, 100, 0)  # 绿色强调不是周期股

    # 股票大涨时间段
    if pd.notna(company_data['股票大涨时间段']) and str(company_data['股票大涨时间段']).strip() and str(
            company_data['股票大涨时间段']) != '暂无信息':
        doc.add_heading('大涨时间段', level=2)
        p = doc.add_paragraph(str(company_data['股票大涨时间段']))
        set_chinese_font(p.runs[0])
        p.runs[0].underline = True  # 下划线强调

    # 大涨期间业绩表现
    if pd.notna(company_data['股票大涨时间段内业绩指标详细描述']) and str(
            company_data['股票大涨时间段内业绩指标详细描述']).strip() and str(
            company_data['股票大涨时间段内业绩指标详细描述']) != '暂无信息':
        doc.add_heading('大涨期间业绩', level=2)
        p = doc.add_paragraph(str(company_data['股票大涨时间段内业绩指标详细描述']))
        set_chinese_font(p.runs[0])

    # 股价变动特点
    if pd.notna(company_data['此公司的股价变动特点']) and str(company_data['此公司的股价变动特点']).strip() and str(
            company_data['此公司的股价变动特点']) != '暂无信息':
        doc.add_heading('股价特点', level=2)
        p = doc.add_paragraph(str(company_data['此公司的股价变动特点']))
        set_chinese_font(p.runs[0])


def add_chart_images(doc, engine, company_data):
    """添加技术分析图表"""
    doc.add_heading('十一、技术分析图表', level=1)

    stock_code = company_data['股票代码']

    # 获取股票图片数据
    images_df = get_stock_images_from_db(engine, stock_code)

    if images_df.empty:
        p = doc.add_paragraph('暂无技术分析图表')
        set_chinese_font(p.runs[0])
        doc.add_paragraph()  # 空行
        return

    # 按照业务关键词顺序插入图片
    for chart_name, chart_conditions in business_keywords.items():
        # 查找匹配的图片
        matching_images = []

        for chart_type, period in chart_conditions:
            # 查找匹配的记录
            for _, row in images_df.iterrows():
                if row['chart_type'] == chart_type and row['period'] == period:
                    matching_images.append(row)

        if matching_images:
            # 添加图表标题
            doc.add_heading(chart_name, level=2)

            # 插入图片
            for image_data in matching_images:
                image_path = image_data['image_path']

                # 检查图片文件是否存在
                if os.path.exists(image_path):
                    try:
                        # 添加图片标题
                        period_name = {
                            'monthly': '月度',
                            'quarterly': '季度',
                            'yearly': '年度',
                            'all': '全周期'
                        }.get(image_data['period'], image_data['period'])

                        p = doc.add_paragraph()
                        p.alignment = WD_ALIGN_PARAGRAPH.CENTER
                        run = p.add_run(f"{period_name}{chart_name}")
                        run.italic = True
                        set_chinese_font(run)

                        # 插入图片
                        doc.add_picture(image_path, width=Inches(6))

                        # 图片后空行
                        doc.add_paragraph()

                    except Exception as e:
                        print(f"插入图片失败 {image_path}: {str(e)}")
                        p = doc.add_paragraph(f"图片加载失败: {os.path.basename(image_path)}")
                        set_chinese_font(p.runs[0])
                else:
                    print(f"图片文件不存在: {image_path}")
                    p = doc.add_paragraph(f"图片文件不存在: {os.path.basename(image_path)}")
                    set_chinese_font(p.runs[0])


def add_report_footer(doc):
    """添加报告页脚"""
    p = doc.add_paragraph()
    p.alignment = WD_ALIGN_PARAGRAPH.RIGHT
    run = p.add_run(f"报告生成时间：{datetime.now().strftime('%Y年%m月%d日 %H:%M:%S')}")
    run.italic = True
    run.font.color.rgb = RGBColor(128, 128, 128)  # 灰色
    set_chinese_font(run)


def generate_company_report(company_data, engine, output_dir='company_reports'):
    """为单个公司生成Word报告"""
    try:
        # 创建输出目录
        if not os.path.exists(output_dir):
            os.makedirs(output_dir)

        # 创建文档
        doc = Document()

        # 设置文档基本样式
        style = doc.styles['Normal']
        style.font.name = '微软雅黑'
        style._element.rPr.rFonts.set(qn('w:eastAsia'), '微软雅黑')

        # 添加各个部分
        add_company_header(doc, company_data)
        add_company_overview(doc, company_data)
        add_business_analysis(doc, company_data)
        add_market_analysis(doc, company_data)
        add_competitive_analysis(doc, company_data)
        add_financial_analysis(doc, company_data)
        add_development_analysis(doc, company_data)
        add_risk_analysis(doc, company_data)
        add_corporate_structure(doc, company_data)
        add_management_info(doc, company_data)
        add_stock_analysis(doc, company_data)

        # 添加技术分析图表（新增功能）
        add_chart_images(doc, engine, company_data)

        add_report_footer(doc)

        # 生成文件名并保存
        filename = f"{company_data['股票代码']}_{company_data['上市公司名称']}_详细分析报告.docx"
        filepath = os.path.join(output_dir, filename)
        doc.save(filepath)

        print(f"已生成报告：{filename}")
        return filepath

    except Exception as e:
        print(f"生成报告失败（{company_data['股票代码']}）: {str(e)}")
        return None


def batch_generate_reports(engine, limit=None):
    """批量生成所有公司报告"""
    # 读取数据
    df = read_company_detail_data(engine, limit)

    if df.empty:
        print("没有找到公司数据")
        return

    success_count = 0
    failed_companies = []

    for index, row in df.iterrows():
        print(f"正在生成 {row['股票代码']} {row['上市公司名称']} 的报告...")

        result = generate_company_report(row, engine)  # 传入engine参数
        if result:
            success_count += 1
        else:
            failed_companies.append(f"{row['股票代码']} {row['上市公司名称']}")

    # 输出统计信息
    print(f"\n报告生成完成！")
    print(f"成功生成：{success_count} 份")
    print(f"失败：{len(failed_companies)} 份")

    if failed_companies:
        print("失败的公司：")
        for company in failed_companies:
            print(f"  - {company}")


if __name__ == '__main__':
    try:
        # 创建数据库连接
        engine = create_database_connection()

        # 批量生成所有公司报告
        batch_generate_reports(engine)  # 先测试10家公司

    except Exception as e:
        print(f"程序执行出错: {str(e)}")